package db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.LinkedList;

import model.City;
import model.Country;
import model.Customer;
import model.Outsider;
import model.Project;
import model.Statistics;

public class DBStatistics implements IFStatistics
{
	private Connection con;

	public DBStatistics()
	{
		con = DBConnection.getInstance().getDBcon();
	}

	@Override
	public LinkedList<Statistics<Project>> getProjectRevenueStatsYear(boolean searchDate, String dateYear, String dateMonth, String locationCountry, String locationCity) throws SQLException
	{
		return getProjectRevenueStats(searchDate, "YEAR(dt.service_deadline) = ?" + (dateMonth.length() > 0 ? " AND DATENAME(month, dt.service_deadline) = ?" : ""), dateYear, dateMonth, locationCountry, locationCity);
	}

	@Override
	public LinkedList<Statistics<Project>> getProjectRevenueStatsPeriod(boolean searchDate, String dateStart, String dateEnd, String locationCountry, String locationCity) throws SQLException
	{
		return getProjectRevenueStats(searchDate, "dt.service_deadline BETWEEN ? AND ?", dateStart, dateEnd, locationCountry, locationCity);
	}

	@Override
	public LinkedList<Statistics<Outsider>> getSupplierDeliveryStats(String locationCountry, String locationCity) throws SQLException
	{
		LinkedList<Statistics<Outsider>> supplierList = new LinkedList<Statistics<Outsider>>();
		String query = "SELECT o.outsider_id, o.outsider_name, o.outsider_active, ct.city_name, cn.country_name, cn.country_tld, (SELECT SUM(is_item_quantity) FROM item_supplier WHERE is_outsider_id = o.outsider_id) AS total_items FROM outsider AS o LEFT JOIN city AS ct ON o.outsider_city_id = ct.city_id LEFT JOIN country AS cn ON ct.city_country_id = cn.country_id WHERE o.outsider_job_id IS NULL" + (locationCountry.length() > 0 ? " AND cn.country_name = ?" + (locationCity.length() > 0 ? " AND ct.city_name = ?" : "") : "")  + " ORDER BY total_items DESC";

		if (DBConnection.doDebug())
			DBConnection.printDebug("getSupplierDeliveryStats", query);

		try
		{
			PreparedStatement ps = con.prepareStatement(query);

			if (locationCountry.length() > 0)
				ps.setString(1, locationCountry);

			if (locationCity.length() > 0)
				ps.setString(2, locationCity);

			ResultSet results = ps.executeQuery();

			while(results.next())
			{
				Outsider newOutsider = new Outsider();

				newOutsider.setOutsiderID(results.getInt("outsider_id"));
				newOutsider.setName(results.getString("outsider_name"));
				newOutsider.setCity(new City(results.getString("city_name"), new Country(results.getString("country_name"), results.getString("country_tld"))));
				newOutsider.setActive(results.getBoolean("outsider_active"));

				Statistics<Outsider> newStatisticsOutsider = new Statistics<Outsider>();

				newStatisticsOutsider.setElement(newOutsider);
				newStatisticsOutsider.setCount(results.getInt("total_items"));

				supplierList.add(newStatisticsOutsider);
			}

			ps.close();
		}
		catch (SQLException e)
		{
			if (DBConnection.doDebug())
			{
				System.out.println("[ERROR] Query exception - getSupplierDeliveryStats - SELECT: " + e);
				e.printStackTrace();
			}
		}

		return supplierList;
	}

	@Override
	public LinkedList<Statistics<Country>> getCountrySupplierStats() throws SQLException
	{
		LinkedList<Statistics<Country>> countryList = new LinkedList<Statistics<Country>>();

		String query = "SELECT country_name, country_tld, SUM(dt.total_items) AS items FROM (SELECT o.outsider_name, cn.country_name, cn.country_tld, (SELECT SUM(is_item_quantity) FROM item_supplier WHERE is_outsider_id = o.outsider_id) AS total_items FROM outsider AS o LEFT JOIN city AS c ON o.outsider_city_id = c.city_id LEFT JOIN country AS cn ON cn.country_id = c.city_country_id) AS dt GROUP BY country_name, country_tld ORDER BY items DESC";

		if (DBConnection.doDebug())
			DBConnection.printDebug("getCountrySupplierStats", query);

		try
		{
			Statement st = con.createStatement();

			ResultSet results = st.executeQuery(query);

			while(results.next())
			{
				Statistics<Country> newStatisticsCountrySupplier = new Statistics<Country>(new Country(results.getString("country_name"), results.getString("country_tld")), results.getInt("items"));

				countryList.add(newStatisticsCountrySupplier);
			}

			st.close();
		}
		catch(SQLException e)
		{
			if (DBConnection.doDebug())
			{
				System.out.println("[ERROR] Query exception - getSupplierDeliveryStats - SELECT: " + e);
				e.printStackTrace();
			}
		}
		return countryList;
	}

	@Override
	public LinkedList<Statistics<Customer>> getCustomerProjectStatsYear(boolean searchDate, String dateYear, String dateMonth, String locationCountry, String locationCity) throws SQLException
	{
		return getCustomerProjectStats(searchDate, "YEAR(s.service_deadline) = ?" + (dateMonth.length() > 0 ? " AND DATENAME(month, s.service_deadline) = ?" : ""), dateYear, dateMonth, locationCountry, locationCity);
	}

	@Override
	public LinkedList<Statistics<Customer>> getCustomerProjectStatsPeriod(boolean searchDate, String dateStart, String dateEnd, String locationCountry, String locationCity) throws SQLException
	{
		return getCustomerProjectStats(searchDate, "s.service_deadline BETWEEN ? AND ?", dateStart, dateEnd, locationCountry, locationCity);
	}

	@Override
	public ArrayList<String> getProjectAllYears()
	{
		ArrayList<String> yearList = new ArrayList<String>();
		String query = "SELECT DISTINCT YEAR(service_deadline) AS year FROM service WHERE service_type = '2' ORDER BY year DESC";

		if (DBConnection.doDebug())
			DBConnection.printDebug("getCustomerProjectAllYears", query);

		try
		{
			Statement st = con.createStatement();

			ResultSet results = st.executeQuery(query);

			while(results.next())
			{
				yearList.add(results.getString("year"));
			}

			st.close();
		}
		catch(SQLException e)
		{
			if (DBConnection.doDebug())
			{
				System.out.println("[ERROR] Query exception - getCustomerProjectAllYears - SELECT: " + e);
				e.printStackTrace();
			}
		}

		return yearList;
	}

	@Override
	public ArrayList<String> getProjectAllMonths(String year)
	{
		ArrayList<String> monthList = new ArrayList<String>();
		String query = "SELECT DISTINCT DATENAME(month, service_deadline) AS month FROM service WHERE YEAR(service_deadline) = ? AND service_type = '2'";

		if (DBConnection.doDebug())
			DBConnection.printDebug("getCustomerProjectAllMonths", query);

		try
		{
			PreparedStatement ps = con.prepareStatement(query);

			ps.setString(1, year);

			ResultSet results = ps.executeQuery();

			while(results.next())
			{
				monthList.add(results.getString("month"));
			}

			ps.close();
		}
		catch(SQLException e)
		{
			if (DBConnection.doDebug())
			{
				System.out.println("[ERROR] Query exception - getCustomerProjectAllMonths - SELECT: " + e);
				e.printStackTrace();
			}
		}

		return monthList;
	}

	@Override
	public ArrayList<String> getProjectCountries() throws SQLException
	{
		ArrayList<String> countryList = new ArrayList<String>();

		String query = "SELECT DISTINCT cn.country_name FROM service AS s LEFT JOIN customer AS c ON s.service_customer_id = c.customer_id LEFT JOIN city AS ct ON c.customer_city_id = ct.city_id LEFT JOIN country AS cn ON ct.city_country_id = cn.country_id";

		if (DBConnection.doDebug())
			DBConnection.printDebug("getCustomerProjectCountries", query);

		try
		{
			PreparedStatement ps = con.prepareStatement(query);

			ResultSet results = ps.executeQuery();

			while (results.next())
			{
				countryList.add(results.getString("country_name"));
			}

			ps.close();
		}
		catch (SQLException e)
		{
			if (DBConnection.doDebug())
			{
				System.out.println("[ERROR] Query exception - getCustomerProjectCountries - SELECT: " + e);
				e.printStackTrace();
			}
		}

		return countryList;
	}

	@Override
	public ArrayList<String> getProjectCities(String country) throws SQLException
	{
		ArrayList<String> cityList = new ArrayList<String>();

		String query = "SELECT DISTINCT ct.city_name FROM service AS s LEFT JOIN customer AS c ON s.service_customer_id = c.customer_id LEFT JOIN city AS ct ON c.customer_city_id = ct.city_id LEFT JOIN country AS cn ON ct.city_country_id = cn.country_id WHERE cn.country_name = ?";

		if (DBConnection.doDebug())
			DBConnection.printDebug("getCustomerProjectCities", query);

		try
		{
			PreparedStatement ps = con.prepareStatement(query);

			ps.setString(1, country);

			ResultSet results = ps.executeQuery();

			while (results.next())
			{
				cityList.add(results.getString("city_name"));
			}

			ps.close();
		}
		catch (SQLException e)
		{
			if (DBConnection.doDebug())
			{
				System.out.println("[ERROR] Query exception - getCustomerProjectCities - SELECT: " + e);
				e.printStackTrace();
			}
		}

		return cityList;
	}

	private LinkedList<Statistics<Project>> getProjectRevenueStats(boolean searchDate, String dateQuery, String dateFirst, String dateSecond, String locationCountry, String locationCity)
	{
		LinkedList<Statistics<Project>> projectList = new LinkedList<Statistics<Project>>();
		String query = "SELECT *, (price_project - price_item) AS revenue, (SELECT SUM(ps_price) FROM project_subcontractor WHERE ps_project_id = project_id) AS subcontractor_money FROM (SELECT s.service_name, s.service_deadline, p.project_id, p.project_item_price AS price_item, o.offer_price AS price_project, per.person_username, ct.city_name, cn.country_name, cn.country_tld FROM project AS p LEFT JOIN offer AS o ON p.project_id = o.offer_service_id LEFT JOIN service AS s ON p.project_id = s.service_id LEFT JOIN person AS per ON s.service_customer_id = per.person_id LEFT JOIN customer AS c ON c.customer_id = s.service_customer_id LEFT JOIN city AS ct ON c.customer_city_id = ct.city_id LEFT JOIN country AS cn ON ct.city_country_id = cn.country_id" + (locationCountry.length() > 0 ? " WHERE cn.country_name = ?" + (locationCity.length() > 0 ? " AND ct.city_name = ?" : "") : "") + ") AS dt " + (searchDate ? " WHERE " + dateQuery : "") + " ORDER BY dt.project_id DESC";

		if (DBConnection.doDebug())
			DBConnection.printDebug("getProjectRevenueStats", query);

		try
		{
			PreparedStatement ps = con.prepareStatement(query);

			int i = 0;

			if (locationCountry.length() > 0)
			{
				ps.setString((i+1), locationCountry);
				i++;
			}

			if (locationCity.length() > 0)
			{
				ps.setString((i+1), locationCity);
				i++;
			}

			if(searchDate)
			{
				ps.setString((i+1), dateFirst);
				i++;

				if (dateSecond.length() > 0)
					ps.setString((i+1), dateSecond);
			}			

			ResultSet results = ps.executeQuery();

			while(results.next())
			{
				Project newProject = new Project();

				newProject.setCustomer(new Customer(0, results.getString("person_username"), new City(results.getString("city_name"), new Country(results.getString("country_name"), results.getString("country_tld")))));
				newProject.setName(results.getString("service_name"));
				newProject.setServiceID(results.getInt("project_id"));
				newProject.setDeadline(results.getString("service_deadline"));

				Statistics<Project> newStatisticsProject = new Statistics<Project>();

				newStatisticsProject.setElement(newProject);
				newStatisticsProject.setPriceItem(results.getDouble("price_item"));
				newStatisticsProject.setPriceProject(results.getDouble("price_project"));
				newStatisticsProject.setRevenue(results.getDouble("revenue"));
				newStatisticsProject.setProjectSubcontractorMoney(results.getDouble("subcontractor_money"));

				projectList.add(newStatisticsProject);
			}

			ps.close();
		}
		catch(SQLException e)
		{
			if (DBConnection.doDebug())
			{
				System.out.println("[ERROR] Query exception - getProjectRevenueStats - SELECT: " + e);
				e.printStackTrace();
			}
		}

		return projectList;
	}

	private LinkedList<Statistics<Customer>> getCustomerProjectStats(boolean searchDate, String dateQuery, String dateFirst, String dateSecond, String locationCountry, String locationCity) throws SQLException
	{
		LinkedList<Statistics<Customer>> customerList = new LinkedList<Statistics<Customer>>();

		String query = "SELECT t.person_id, t.person_username, cn.country_name, cn.country_tld, ct.city_name, SUM(t.price_item) AS total_item, SUM(t.price_project) AS total_project, SUM(t.revenue) AS total_revenue, COUNT(t.project_id) AS project_count FROM (SELECT *, (price_project - price_item) AS revenue FROM (SELECT c.person_username, c.person_id, p.project_id, p.project_item_price AS price_item, o.offer_price AS price_project FROM project AS p LEFT JOIN offer AS o ON p.project_id = o.offer_service_id LEFT JOIN service AS s ON s.service_id = p.project_id LEFT JOIN person AS c ON c.person_id = s.service_customer_id" + (searchDate ? " WHERE " + dateQuery : "") + ") AS dt) AS t LEFT JOIN customer AS cus ON cus.customer_id = t.person_id LEFT JOIN city AS ct ON cus.customer_city_id = ct.city_id LEFT JOIN country AS cn ON ct.city_country_id = cn.country_id " + (locationCountry.length() > 0 ? " WHERE cn.country_name = ?" + (locationCity.length() > 0 ? " AND ct.city_name = ?" : "") : "") + " GROUP BY t.person_id, t.person_username, cn.country_name, cn.country_tld, ct.city_name ORDER BY project_count DESC";

		if (DBConnection.doDebug())
			DBConnection.printDebug("getCustomerProjectStats", query);

		try
		{
			PreparedStatement ps = con.prepareStatement(query);

			int i = 0;

			if(searchDate)
			{
				ps.setString(1, dateFirst);
				i++;

				if (dateSecond.length() > 0)
				{
					ps.setString(2, dateSecond);
					i++;
				}
			}

			if (locationCountry.length() > 0)
				ps.setString((i+1), locationCountry);

			if (locationCity.length() > 0)
				ps.setString((i+2), locationCity);

			ResultSet results = ps.executeQuery();

			while(results.next())
			{
				Statistics<Customer> newCustomerStats = new Statistics<Customer>();

				newCustomerStats.setElement(new Customer(results.getInt("person_id"), results.getString("person_username"), new City(results.getString("city_name"), new Country(results.getString("country_name"), results.getString("country_tld")))));
				newCustomerStats.setCount(results.getInt("project_count"));
				newCustomerStats.setPriceItem(results.getDouble("total_item"));
				newCustomerStats.setPriceProject(results.getDouble("total_project"));
				newCustomerStats.setRevenue(results.getDouble("total_revenue"));

				customerList.add(newCustomerStats);
			}

			ps.close();
		}
		catch (SQLException e)
		{
			if (DBConnection.doDebug())
			{
				System.out.println("[ERROR] Query exception - getCustomerProjectStats - SELECT: " + e);
				e.printStackTrace();
			}
		}

		return customerList;
	}

	@Override
	public ArrayList<String> getSupplierDeliveryCountries() throws SQLException
	{
		ArrayList<String> countryList = new ArrayList<String>();

		String query = "SELECT DISTINCT cn.country_name FROM outsider AS o LEFT JOIN city AS ct ON o.outsider_city_id = ct.city_id LEFT JOIN country AS cn ON cn.country_id = ct.city_country_id ORDER BY cn.country_name ASC";

		if (DBConnection.doDebug())
			DBConnection.printDebug("getSupplierDeliveryCountries", query);

		try
		{
			Statement st = con.createStatement();

			ResultSet results = st.executeQuery(query);

			while(results.next()) 
			{
				countryList.add(results.getString("country_name"));
			}

			st.close();
		}
		catch (SQLException e)
		{
			if (DBConnection.doDebug())
			{
				System.out.println("[ERROR] Query exception - getSupplierDeliveryCountries - SELECT: " + e);
				e.printStackTrace();
			}
		}

		return countryList;
	}

	@Override
	public ArrayList<String> getSupplierDeliveryCities(String country) throws SQLException
	{
		ArrayList<String> cityList = new ArrayList<String>();

		String query = "SELECT DISTINCT ct.city_name FROM outsider AS o LEFT JOIN city AS ct ON o.outsider_city_id = ct.city_id LEFT JOIN country AS cn ON cn.country_id = ct.city_country_id WHERE cn.country_name = ? ORDER BY ct.city_name ASC";

		if (DBConnection.doDebug())
			DBConnection.printDebug("getSupplierDeliveryCities", query);

		try
		{
			PreparedStatement ps = con.prepareStatement(query);

			ps.setString(1, country);

			ResultSet results = ps.executeQuery();

			while(results.next())
			{
				cityList.add(results.getString("city_name"));
			}

			ps.close();
		}
		catch (SQLException e)
		{
			if (DBConnection.doDebug())
			{
				System.out.println("[ERROR] Query exception - getSupplierDeliveryCities - SELECT: " + e);
				e.printStackTrace();
			}
		}

		return cityList;
	}
}